package Classes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

/**
 * 
 * @author Dagny Jackson
 * General Agencies Class creation
 * everything on this page created by Dagny
 */
public class Agencies 
{
	//===============================Properties=======================================
	// start Dagny Jackson properties
	private int agencyId;
	private String agencyAddress;
	private String agencyCity;
	private String agencyProvince;
	private String agencyPostalCode;
	private String agencyCountry;
	private String agencyPhone;
	private String agencyFax;
	// end Dagny Jackson properties
	
	//===============================Constructors=======================================
	
	//===============================Getters and Setters================================
	// start Dagny Jackson getters and setters
	public int getAgencyId() 
	{
		return agencyId;
	}
	public void setAgencyId(int agencyId) 
	{
		this.agencyId = agencyId;
	}
	
	
	public String getAgencyAddress() 
	{
		return agencyAddress;
	}
	public void setAgencyAddress(String agencyAddress) 
	{
		this.agencyAddress = agencyAddress;
	}
	
	
	public String getAgencyCity() 
	{
		return agencyCity;
	}
	public void setAgencyCity(String agencyCity) 
	{
		this.agencyCity = agencyCity;
	}
	
	
	public String getAgencyProvince() 
	{
		return agencyProvince;
	}
	public void setAgencyProvince(String agencyProvince) 
	{
		this.agencyProvince = agencyProvince;
	}
	
	
	public String getAgencyPostalCode() 
	{
		return agencyPostalCode;
	}
	public void setAgencyPostalCode(String agencyPostalCode) 
	{
		this.agencyPostalCode = agencyPostalCode;
	}
	
	
	public String getAgencyCountry() 
	{
		return agencyCountry;
	}
	public void setAgencyCountry(String agencyCountry) 
	{
		this.agencyCountry = agencyCountry;
	}
	
	
	public String getAgencyPhone() 
	{
		return agencyPhone;
	}
	public void setAgencyPhone(String agencyPhone) 
	{
		this.agencyPhone = agencyPhone;
	}
	
	
	public String getAgencyFax() 
	{
		return agencyFax;
	}
	public void setAgencyFax(String agencyFax) 
	{
		this.agencyFax = agencyFax;
	}
	// end Dagny Jackson getters and setters	
	
	//===============================Override To String Method==========================
	//start Dagny Jackson override

	@Override
	public String toString() 
	{
	    String result = agencyAddress + " - " + agencyCity;
	    return result;
	}
	
	//end Dagny Jackson override
	
	//===============================Methods============================================

	//start Dagny Jackson methods
	
	//this method connects to the DB
	public Connection connectToDb()
	{
		try 
		{	
			//load the driver for oracle
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			//establish a connection to the database
			//first string is location of the database (url)
			//second string is user name
			//third string is password
			Connection agentConnection =  DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g", "ictoosd", "ictoosd");
						
			//String defaultSqlQuery = "alter session set NLS_COMP=LINGUISTIC";
			
			
			return agentConnection;
		} 
		catch (ClassNotFoundException e) 
		{
			e.printStackTrace();
			Connection noConnectionMade = null;
			return noConnectionMade;
		} catch (SQLException sqle) 
		{
			sqle.printStackTrace();
			Connection noConnectionMade = null;
			return noConnectionMade;
		}	
	}
	
	//this function returns Agency ArrayLists of requested Agencies in the database
	//based on query string passed through
	public ArrayList<Agencies> agenciesAr(String sqlQuery)
	{
		ArrayList<Agencies> agencyArrayList = new ArrayList<Agencies>();

		Connection listConn = connectToDb();
		if (listConn != null)
		{
			try {
				Statement stmnt = listConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery(sqlQuery);
	
				while (rs.next())
				{
					//get the item and put it into a new Agent
					Agencies anAgency = new Agencies();
					anAgency.setAgencyId(Integer.parseInt(rs.getString(1)));
					anAgency.setAgencyAddress(rs.getString(2));
					anAgency.setAgencyCity(rs.getString(3));
					anAgency.setAgencyProvince(rs.getString(4));
					anAgency.setAgencyPostalCode(rs.getString(5));
					anAgency.setAgencyCountry(rs.getString(6));
					anAgency.setAgencyPhone(rs.getString(7));
					anAgency.setAgencyFax(rs.getString(8));
					
					//add agent to the 
					agencyArrayList.add(anAgency);

				}
				//close the connection
				listConn.close();
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return agencyArrayList;
		}
		else
		{
			System.out.println("No SQL connection was made.");
			return agencyArrayList;
		}
	}
	
	//this function returns an Agency Array from any ArrayList of Agencies
	public Agencies[] createAgencyArray(ArrayList<Agencies> agencyAr)
	{
		int lengthOfAgenciesArrayList = agencyAr.size();
		Agencies [] allAgencies = new Agencies[lengthOfAgenciesArrayList];
		for (int a = 0; a <lengthOfAgenciesArrayList; a++)
		{
			allAgencies[a] = agencyAr.get(a);
		}
		return allAgencies;
	}
	
	//this function returns a Vector containing all the agencies
	public Vector<Object> agenciesVector(String sqlQuery)
	{
		Vector<Object> agenciesVector = new Vector<Object>();
		
		//create a fake agency for the first element:
		Agencies fakeAgency = new Agencies();
		fakeAgency.setAgencyAddress("-");
		fakeAgency.setAgencyCity("Select an Agency - -");
		fakeAgency.setAgencyId(0);
		//agenciesVector.addElement("--Select an Agency--");
		//add the fake agency to the vector:
		agenciesVector.addElement(fakeAgency);
		
		Connection listConn = connectToDb();
		if (listConn != null)
		{
			try {
				Statement stmnt = listConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery(sqlQuery);

				while (rs.next())
				{
					//get the item and put it into a new Agency
					Agencies anAgency = new Agencies();
					anAgency.setAgencyId(Integer.parseInt(rs.getString(1)));
					anAgency.setAgencyAddress(rs.getString(2));
					anAgency.setAgencyCity(rs.getString(3));
					anAgency.setAgencyProvince(rs.getString(4));
					anAgency.setAgencyPostalCode(rs.getString(5));
					anAgency.setAgencyCountry(rs.getString(6));
					anAgency.setAgencyPhone(rs.getString(7));
					anAgency.setAgencyFax(rs.getString(8));
					
					//add agency to the vector
					//originally had anAgency.toString(), but that's making 
					//what's stored in the a striong, so I'm getting errors
					//when trying to cast it back to an Agency
					agenciesVector.addElement(anAgency);
				}
				listConn.close();
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return agenciesVector;
		}
		else
		{
			System.out.println("No SQL connection was made.");
			return agenciesVector;
		}
	}
	
	
	
	
	//this function returns the next sequential id for a new agency added to the database
	public int getNextNewAgecyId()
	{
		int newAgencyId = 0;
		
		//connect to database
		Connection saveConn = connectToDb();
		
		if (saveConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = saveConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("select max(agencyid) from agencies");
				//SELECT TOP 1 AgentId FROM agents ORDER BY AgentId DESC
				
				
				while (rs.next())
				{
					//grab the last number and add one to it to get next number
					newAgencyId = rs.getInt(1) + 1;
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				
				//close the connection
				saveConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		
		return newAgencyId;
	}
	
	//this method passes all the information from the fields
	//in the form and adds a brand new agency
	public void addAgency(int agencyId, String address, String city, String province, String postal, String country, String phone, String fax)
	{
		Connection addConn = connectToDb();
		if (addConn != null)
		{
			try 
			{
				String addString = "INSERT INTO agencies (AgencyId, AgncyAddress, AgncyCity, AgncyProv," +
						"AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax) VALUES (?,?,?,?,?,?,?,?)";
	        
		        PreparedStatement addStmnt = addConn.prepareStatement(addString);
		       
		        addStmnt.setInt(1, agencyId);
		        addStmnt.setString(2, address);
		        addStmnt.setString(3, city);
		        addStmnt.setString(4, province);
		        addStmnt.setString(5, postal);
		        addStmnt.setString(6, country);
		        addStmnt.setString(7, phone);
		        addStmnt.setString(8, fax);
		        
		        int rowsChanged = addStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        addConn.commit();
		        addConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}

	//this method passes all the information from the fields
	//in the form and updates the selected agency
	public void updateAgency(int agencyId, String address, String city, String province, String postal, String country, String phone, String fax)
	{
		Connection updateConn = connectToDb();
		if (updateConn != null)
		{
			try 
			{
				String updateString = "UPDATE agencies set "
		           	+ "AgncyAddress=?, AgncyCity=?, AgncyProv=?, "
		            + "AgncyPostal=?, AgncyCountry=?, "
		            + "AgncyPhone=?, AgncyFax=? "
		            + " WHERE AgencyId=?";
		        
		        PreparedStatement updateStmnt = updateConn.prepareStatement(updateString);
		       
		        updateStmnt.setString(1, address);
		        updateStmnt.setString(2, city);
		        updateStmnt.setString(3, province);
		        updateStmnt.setString(4, postal);
		        updateStmnt.setString(5, country);
		        updateStmnt.setString(6, phone);
		        updateStmnt.setString(7, fax);
		        updateStmnt.setInt(8, agencyId);
		        
		        int rowsChanged = updateStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        updateConn.commit();
		        updateConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}

	//this method deletes the selected agency
	public void deleteAgency(int agencyId)
	{
		Connection deleteConn = connectToDb();
		if (deleteConn != null)
		{
			try 
			{
				String deleteString = "DELETE FROM agencies WHERE agencyid=?";
		        
		        
		        PreparedStatement deleteStmnt = deleteConn.prepareStatement(deleteString);
		       
		        deleteStmnt.setInt(1, agencyId);
		        
		        int rowsChanged = deleteStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        deleteConn.commit();
		        deleteConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}

	//this function returns a listing of all the provinces currently existing in the AGENCIES table
	public ArrayList<String> loadProvinces()
	{
		//create an ArrayList to hold the agent ids
		ArrayList<String> provinceAr = new ArrayList<String>();
		provinceAr.add("--Select a Province--");
		
		//connect to database
		Connection loadConn = connectToDb();
		
		if (loadConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("SELECT DISTINCT agncyprov FROM agencies ORDER BY agncyprov");
				
				
				while (rs.next())
				{
					//get the item and put it into the ArrayList
					provinceAr.add(rs.getString(1));
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				loadConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		return provinceAr;
	}

	//this function returns a listing of all the cities currently existing in the AGENCIES table
	public ArrayList<String> loadCities()
	{
		//create an ArrayList to hold the agent ids
		ArrayList<String> agencyAr = new ArrayList<String>();
		agencyAr.add("--Select a City--");
		
		//connect to database
		Connection loadConn = connectToDb();
		
		if (loadConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("SELECT DISTINCT agncycity FROM agencies ORDER BY agncycity");
				
				
				while (rs.next())
				{
					//get the item and put it into the ArrayList
					agencyAr.add(rs.getString(1));
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				loadConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		return agencyAr;
	}
	
	//this function returns a listing of all the cities currently existing in the AGENCIES table
	public ArrayList<String> loadCountries()
	{
		//create an ArrayList to hold the agent ids
		ArrayList<String> agencyAr = new ArrayList<String>();
		agencyAr.add("--Select a Country--");
		
		//connect to database
		Connection loadConn = connectToDb();
		
		if (loadConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("SELECT DISTINCT agncycountry FROM agencies ORDER BY agncycountry");
				
				
				while (rs.next())
				{
					//get the item and put it into the ArrayList
					agencyAr.add(rs.getString(1));
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				loadConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		return agencyAr;
	}

	
	//this function returns the specific information of a chosen agent
	public ArrayList<String> loadAgencyInformation(int agencyid)
	{
		ArrayList<String> agencyInfoAr = new ArrayList<String>();

		Connection loadConn = connectToDb();
		if (loadConn != null)
		{
			try 
			{
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//create a reference to the AgentFrame
				
				//Call the statement object
				ResultSet rs = stmnt.executeQuery("SELECT * FROM agencies WHERE agencyid=" + agencyid);
				
				//while there is a next row in the result set
				while (rs.next())
				{
					//process the columns in each row
					//this is NOT A zero  based index. Starts with 1!
					for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) 
					{
						agencyInfoAr.add(rs.getString(i));
					}
				}
				loadConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	return agencyInfoAr;
	}
	//end Dagny Jackson methods
}
